﻿namespace nToDo
{
    using System;
    using System.Collections.ObjectModel;
    using System.Data;
    using System.Data.SqlServerCe;
    using System.IO;
    using System.Reflection;
    using System.Collections.Generic;

    public class DataAccess
    {
        private static DataAccess dataAccess;
        public static DataAccess GetInstance()
        {
            if (dataAccess == null)
            {
                string applicationDbPath = Path.Combine(Properties.Settings.Default.DbPath, "nTodo.sdf");
                if (!File.Exists(applicationDbPath))
                {
                    throw new FileNotFoundException("Database file not found", "ntodo.sdf");
                }
                string connectionString = string.Format(Properties.Settings.Default.Connection, applicationDbPath);
                dataAccess = new DataAccess(connectionString);
            }
            return dataAccess;
        }

        private SqlCeConnection connection;
        private SqlCeCommand command;
        private Dictionary<string, object> parameters = null;
        private string connectionString;
        protected DataAccess(string connectionString)
        {
            this.connectionString = connectionString;
        }

        protected IDataReader ExecuteReader(string Query)
        {

            using (this.command = new SqlCeCommand(Query, GetConnection()))
            {
                if (this.parameters != null)
                {
                    foreach (KeyValuePair<string, object> parameter in this.parameters)
                    {
                        this.command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }
                return this.command.ExecuteReader();
            }
        }

        private SqlCeConnection GetConnection()
        {
            if (this.connection == null || this.connection.State == ConnectionState.Closed)
            {
                this.connection = new SqlCeConnection(this.connectionString);
                this.connection.Open();
            }
            return this.connection;
        }

        public void CloseConnection()
        {
            if (this.connection != null || this.connection.State != ConnectionState.Closed)
            {
                this.connection.Close();
                this.connection.Dispose();
            }
        }

        public Collection<Task> GetTasks(DateTime taskDate)
        {
            string query = string.Format("SELECT TaskId, TaskDate, TaskPriority, TaskNotes, TaskDone FROM Tasks WHERE TaskDate = '{0}' ORDER BY TaskPriority ASC", taskDate.Date);
            IDataReader reader = this.ExecuteReader(query);
            Collection<Task> result = new Collection<Task>();
            if (!reader.IsClosed)
            {
                while (reader.Read())
                {
                    result.Add(new Task()
                    {
                        DueDate = Convert.ToDateTime(reader["TaskDate"]),
                        IsDone = Convert.ToBoolean(reader["TaskDone"]),
                        Notes = reader["TaskNotes"].ToString(),
                        Priority = Convert.ToInt16(reader["TaskPriority"]),
                        TaskId = reader["TaskId"].ToString(),
                    }
                    );
                }
            }
            return result;
        }

        public void AddTask(Task task)
        {
            string query = "INSERT INTO Tasks(TaskDate, TaskPriority, TaskNotes) VALUES(@taskDate,@taskPriority,@taskNotes)";
            this.parameters = new Dictionary<string, object>(3);
            this.parameters.Add("@taskDate", task.DueDate.Date);
            this.parameters.Add("@taskPriority", task.Priority);
            this.parameters.Add("@taskNotes", task.Notes);
            this.ExecuteReader(query);
        }
        public void RemoveTask(string taskId)
        {
            string query = string.Format("DELETE FROM Tasks WHERE TaskId = '{0}'", taskId);
            this.ExecuteReader(query);
        }
        public void MarkAsDone(string taskId)
        {
            string query = string.Format("UPDATE Tasks SET TaskDone = 1 WHERE TaskId = '{0}'", taskId);
            this.ExecuteReader(query);
        }
        public Task GetTask(string TaskId)
        {
            string query = string.Format("SELECT TaskId, TaskDate, TaskPriority, TaskNotes, TaskDone FROM Tasks WHERE TaskId = '{0}'", TaskId);
            IDataReader reader = this.ExecuteReader(query);
            Task result = default(Task);
            if (!reader.IsClosed)
            {
                while (reader.Read())
                {
                    result = new Task()
                    {
                        DueDate = Convert.ToDateTime(reader["TaskDate"]),
                        IsDone = Convert.ToBoolean(reader["TaskDone"]),
                        Notes = reader["TaskNotes"].ToString(),
                        Priority = Convert.ToInt16(reader["TaskPriority"]),
                        TaskId = reader["TaskId"].ToString(),
                    };
                }
            }
            return result;
        }
    }
}
